/*
 * $RCSfile: Oracle2Postgres.java,v $
 * $Revision: 1.7 $
 *
 * Comments:
 *
 * (C) Copyright Biuro-Technika 2007.  All rights reserved.
 * THIS IS UNPUBLISHED PROPRIETARY SOURCE CODE OF Biuro-Technika
 * The copyright notice above does not evidence any
 * actual or intended publication of such source code.
 *
 * $Author: michal $          $Locker:  $
 * $Date: 2010/05/24 10:42:42 $
 * $Log: Oracle2Postgres.java,v $
 * Revision 1.7  2010/05/24 10:42:42  michal
 * parametryzacja kodu
 *
 * Revision 1.6  2009/09/22 11:54:22  michal
 * poprawiono pobieranie schem
 *
 * Revision 1.5  2009/05/05 13:24:01  michal
 * eliminacja tworzenia instancji obiektu Boolean
 *
 * Revision 1.4  2009/01/02 13:32:21  michal
 * zmiana statycznej nazwy schemy centralnej na dynamiczna
 *
 * Revision 1.3  2008/04/01 12:38:35  muzzy
 * warningi, kosmetyka kodu
 *
 * Revision 1.2  2007/09/07 09:22:42  dominik
 * Przesuniecie bloku try/catch przy pobieraniu kolumn
 *
 * Revision 1.1  2007/08/24 09:14:29  dominik
 * Migracja z Oracle do Postgresql
 *
 */

package parasoft.centrap.migration;

import java.math.BigDecimal;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.List;
import java.util.Vector;

import parasoft.centrap.connection.DbConnection;
import parasoft.centrap.connection.DebuggablePreparedStatement;
import parasoft.centrap.db.DbException;

public class Oracle2Postgres {

	public final static String POSTGRES_PROPERTIES = "POSTGRES_PROPERTIES";
	public final static String ORACLE_PROPERTIES = "ORACLE_PROPERTIES";
	public final static String PROPERTIES_PREFIX = "centrap.";
	public final static String DBMANAGER = PROPERTIES_PREFIX + "dbmanager";

	private final DbConnection postgresConnection = null;

	private final DbConnection oracleConnection = null;

	//oid -> Schema, Table or Column
	private final Vector<Schema> structure = new Vector<Schema>();

	public Oracle2Postgres() {
		System.out.println("Initializing...");
		try {
			System.out.println(System.getProperty(POSTGRES_PROPERTIES, "../") +"postgres.properties");

			System.err.print("Initializing DbManager...");
			System.err.print("OK\n");

			System.err.print("OK\n");
		}
		catch (final UnsatisfiedLinkError e) {
			try {
				System.load(System.getProperty(DBMANAGER) + "/libdbmanager.so");
			}
			catch (final UnsatisfiedLinkError er) {
				System.err.println(
				"Error initializing dbmanager: Error loading native library");
				return;
			}
		}
		try {

			System.err.print("Initializing DbManager...");
			System.err.print("OK\n");

		}
		catch (final Exception ex) {
			System.err.println("Error initializing dbmanager: " + ex);
		}

	}

	public void connectToPostgres()
	{

		System.err.print("Getting connection...");
		System.err.print("OK\n");
	}

	public void connectToOracle()
	{

		System.err.print("Getting connection...");
		System.err.print("OK\n");
	}

	public void getStructure()
	{
		try {
			System.out.println("Pobieram strukture bazy danych...");
			getSchemas();
			getTables();
			getColumns();
			System.out.println("OK");

		} catch(final SQLException e){
			e.printStackTrace();
		}
	}

	private void getSchemas() throws SQLException
	{
		System.out.print("Pobieram schemy...");
		final List<String> schamasList = oracleConnection.getSchemas();
		for (final String string : schamasList) {
			final String schema_name = string;
			if(oracleConnection.existsTable(schema_name, "TOWARY")){
				final Schema schema = new Schema(schema_name);
					structure.add(schema);
			}
		}
		System.out.println("OK");
	}

	private void getTables()
	{
		System.out.print("Pobieram tabele...");
		try {
			for (final Schema schema : structure) {
				final List<String> tableList = oracleConnection.getTables(schema.getSchemaName());
				for (final String string : tableList) {
					final String tableName = string;
					final Table table = new Table(tableName);
					schema.addTable(table);
				}
			}
		} catch(final SQLException e){
			e.printStackTrace();
		}
		System.out.println("OK");
	}

	private void getColumns()
	{
		System.out.println("Pobieram kolumny...");
		for (final Schema schema : structure) {
			final Vector<Table> tables = schema.getTables();
			for (final Table table : tables) {
				System.out.println("Tablica "+schema.getSchemaName()+"."+ table.getTableName());

				List<Column> columnList = null;
				try {
					columnList = oracleConnection.getColumns(schema.getSchemaName(), table.getTableName());

					//kopiuje kolumny do odpowiednij tablicy
					if(columnList != null) {
						for (final Column column2 : columnList) {
							final Column column = column2;
							table.addColumn(column);
						}
					}

					//wyszukuje klucz glowny
					final String primeryKey = oracleConnection.getPrimaryKey(schema.getSchemaName(), table.getTableName());

					//ustawiam klucz glowny dla odpowiednij kolumny
					//System.out.println(schema.getSchemaName() +"."+table.getTableName() +" -> "+primeryKey);
					if(primeryKey != null) {
						for (final Column column2 : columnList) {
							final Column column = column2;
							if(primeryKey.equals(column.getColumnName())) {
								column.setPrimaryKey(true);
								break;
							}
						}
					}
				} catch (final SQLException e) {
					e.printStackTrace();
				}
			}
		}
		System.out.println("OK");
	}

	public void createStructure()
	{
		try {
			System.out.println("Zaczynam tworzy� strukture schem...");
			createSchemas();
			createTables();
			System.out.println("OK");
		} catch (final SQLException e) {
			e.printStackTrace();
		}
	}

	public void createSchemas() throws SQLException
	{
		System.out.println("Tworze scheme...");
		for(int i =0; i<structure.size();i++ ) {
			final Schema schema = structure.get(i);
			if(postgresConnection.existsSchema(schema.getSchemaName())) {
				System.out.println("Schemat "+schema.getSchemaName()+" juz istnieje - pomijam go!");
			} else {
				createSchema(schema.getSchemaName());
			}
		}
		System.out.println("OK");
	}

	private void createSchema(final String schema)
	{
		try {
			postgresConnection.createSchema(schema);
			System.out.println("Schemat: "+schema+" zosta� utworzony");
		} catch (final SQLException e) {
			e.printStackTrace();
		}
	}

	public void createTables()
	{
		System.out.print("Tworze tabele...");
		for (final Schema schema : structure) {
			for (final Table table : schema.getTables()) {
				int i = 0;
				final int columnsNumber = table.getColumns().size();
				String fields = "(";
				String key = "";
				for (final Column column : table.getColumns()) {
					final int typ = column.getSqlType();
					fields += column.getColumnName();
					if(column.getColumnName().equalsIgnoreCase("STATUS")) { //Types.DECIMAL
					}
					else {
					}
					if((typ==Types.VARCHAR) || (typ==Types.CHAR) || (typ==Types.LONGVARCHAR) ) {
						fields += "("+column.getLength()+")";
					}

					if (column.isPrimaryKey()){
						key = column.getColumnName();
						fields += " PRIMARY KEY";
					}
					i++;
					if (i < columnsNumber){
						fields += ", ";
					}
				}
				fields += ")";
				try {
					System.out.println("CREATE TABLE " + schema.getSchemaName()+"."+table.getTableName() + " " + fields);
					postgresConnection.createTable(schema.getSchemaName()+"."+table.getTableName(), fields);
				} catch (final Exception e) {
					e.printStackTrace();
				}

				try {
					if (key.length() > 0){
						System.out.println("CREATE SEQUENCE " + schema.getSchemaName()+"." + key +
							" START WITH 1 INCREMENT BY 1" +
							" MINVALUE 1 NOMAXVALUE NOCYCLE ORDER");
						postgresConnection.createSequence(schema.getSchemaName()+"."+key);
					}
				} catch (final Exception e) {
					e.printStackTrace();
				}
			}
		}
		System.out.print("OK");
	}

	public void setStatement(final DebuggablePreparedStatement stmt, final int index, Object value, final int type)
	throws SQLException, DbException
	{
	    switch(type) {
	    	case Types.BIGINT:
        		if(value==null) { value = new Long(0); }
	            stmt.setLong(index, ((Long)value).longValue());
	            break;

        	case Types.NUMERIC:
        		if(value==null) { value = new BigDecimal(0); }
	            stmt.setLong(index, ((BigDecimal)value).longValue());
	            break;

	        case Types.INTEGER:
        		if(value==null) { value = new Integer(0); }
	            stmt.setInt(index, ((Integer)value).intValue());
	            break;

	        case Types.SMALLINT:
        		if(value==null) { value = new Short((short) 0); }
	            stmt.setShort(index, ((Short)value).shortValue());
	            break;

	        case Types.CHAR:
	        case Types.VARCHAR:
	                stmt.setString(index, ( (String) value));
	            break;

	        case Types.REAL:
	        case Types.FLOAT:
	        case Types.DOUBLE:
        		if(value==null) { value = new Double(0); }
	            stmt.setDouble(index, ((Double)value).doubleValue());
	            break;

	        case Types.BOOLEAN:
        		if(value==null) { value = Boolean.valueOf(false); }
	            stmt.setBoolean(index, ((Boolean)value).booleanValue());
	            break;

	        case Types.DATE:
//        		if(value==null) { value = new java.sql.Date(Calendar.getInstance().getTime().getTime()); }
	            stmt.setDate(index, ((Date)value));
	            break;

	        case Types.TIMESTAMP:
//        		if(value==null) { value = new Timestamp(Calendar.getInstance().getTime().getTime()); }
	            stmt.setTimestamp(index, ((Timestamp)value));
	            break;

	        default:
	            throw new DbException("Unknown object type! "+ type );
	    }
	}

	public void moveData(final String schema, final String table)
	{
		try {
			String query = "SELECT COUNT(*) FROM " + schema + "." + table;
			ResultSet rs = oracleConnection.createStatement().executeQuery(query);
			long rows = 0;
			if(rs.next()) {
				rows = rs.getLong(1);
			}

			final int offsetValue = 1000; //wartosc o ktora bedziemy przesuwac limit - zabezpieczenie przed bledem braku pamieci
			if(rows > offsetValue) {
				final String limit = " WHERE ROWNUM <= ";
				final String offset = " MINUS ";
				for(int j=0; j*offsetValue < rows; j++) {
					final String mainQuery = "SELECT * FROM " + schema + "." + table;
					query = "";
					if(j==0) {
						query += mainQuery + limit + offsetValue ;
					}
					else {
						query += mainQuery + limit + ((j+1)*offsetValue) + offset + mainQuery + limit + j*offsetValue;
					}

					System.out.println(query);

					rs = oracleConnection.createStatement().executeQuery(query);
					final ResultSetMetaData metaData = rs.getMetaData();

					final int columnCount = metaData.getColumnCount();
					String insertInto = "INSERT INTO " + schema + "." + table + "(" +metaData.getColumnName(1);

					for (int i = 2; i < columnCount+1; i++) {
						insertInto += ", "+metaData.getColumnName(i);
					}
					insertInto += ") VALUES (";
					while (rs.next()) {
						String insert = insertInto + " ? ";
						for (int i = 2; i < columnCount+1; i++) {
							insert += ",? ";
						}
						insert += ")";

						DebuggablePreparedStatement stmt = null;
						postgresConnection.startTransaction();
						try {
							stmt = postgresConnection.prepareStatement(insert);

							int param=1;
							for (int i = 1; i < columnCount+1; i++) {
								setStatement(stmt,param++,rs.getObject(i),metaData.getColumnType(i));
							}
							stmt.executeUpdate();

						} finally {
							postgresConnection.closeStatement(stmt);
							postgresConnection.finishTransaction();
						}
					}
					oracleConnection.closeAllStatements();
				}
			}
			else {
				query = "SELECT * FROM " + schema + "." + table;

				System.out.println(query);

				rs = oracleConnection.createStatement().executeQuery(query);
				final ResultSetMetaData metaData = rs.getMetaData();

				final int columnCount = metaData.getColumnCount();
				String insertInto = "INSERT INTO " + schema + "." + table + "(" +metaData.getColumnName(1);

				for (int i = 2; i < columnCount+1; i++) {
					insertInto += ", "+metaData.getColumnName(i);
				}
				insertInto += ") VALUES (";
				while (rs.next()){
					String insert = insertInto + " ? ";
					for (int i = 2; i < columnCount+1; i++) {
						insert += ",? ";
					}
					insert += ")";

					DebuggablePreparedStatement stmt = null;
					postgresConnection.startTransaction();
					try {
						stmt = postgresConnection.prepareStatement(insert);

						int param=1;
						for (int i = 1; i < columnCount+1; i++) {
							setStatement(stmt,param++,rs.getObject(i),metaData.getColumnType(i));
						}
						stmt.executeUpdate();

					} finally {
						postgresConnection.closeStatement(stmt);
						postgresConnection.finishTransaction();
					}
				}
				oracleConnection.closeAllStatements();
			}
		} catch (final SQLException e) {
			e.printStackTrace();
		} catch (final DbException e) {
			e.printStackTrace();
		}
	}

	public void moveData()
	{
		for(int i=0;i<structure.size();i++) {
			final Schema schema = structure.get(i);
			final Vector<Table> tables = schema.getTables();
			for(int j=0;j<tables.size();j++) {
				final Table table = tables.get(j);
				moveData(schema.getSchemaName(), table.getTableName());
			}
		}
	}

	private Vector<Index> getIndexes()
	{
		System.out.print("Pobieram indexy...");
		final Vector<Index> indexes = new Vector<Index>();
		try {
			for (final Schema schema : structure) {
				final Vector<Table> table = schema.getTables();

				System.out.println("Pobieram indeksy dla schemy "+schema.getSchemaName());
				if (schema.getSchemaName().equalsIgnoreCase("")) {
				for (final Table table1 : table) {
					indexes.addAll(oracleConnection.getIndexs(schema.getSchemaName(), table1.getTableName()));
				}
				}
			}
		} catch (final SQLException e1) {
			e1.printStackTrace();
		}
		return indexes;
	}

	private void createIndexes(final Vector<Index> indexes)
	{
		System.out.print("Tworze indexy...");
		for (final Index index2 : indexes) {
			final Index index = index2;

			final Vector<String> columns = new Vector<String>();
			columns.addAll(index.getColumns());

			try {
				postgresConnection.createIndex(index.getIndexName(), index.getSchema()+"."+index.getTable(), columns, index.isUnique());
			} catch (final SQLException e) {
				e.printStackTrace();
			}

		}
		System.out.print("OK");
	}


	public void destroy()
	{
	}

	/**
	 * @param args
	 */
	public static void main(final String[] args) {

		final Oracle2Postgres migration = new Oracle2Postgres();
		migration.connectToPostgres();
		migration.connectToOracle();

		//pobieram i tworze strukture bazy danych
		migration.getStructure();
		migration.createStructure();

		//przenosze dane
		migration.moveData();

		//zakladam indeksy
		final Vector<Index> indexes = migration.getIndexes();
		migration.createIndexes(indexes);

		System.out.println("=========================");
		migration.destroy();

	}

}
